from DAL.sqlHelper import (get_recharge_conn as conn_a,SQL_ADD_FIELD_VALUES
,SQL_UPDATE_SET,SQL_ORDER,SQL_WHERE,SQL_LIKE)
import xlrd
"""
充值统计模块，包括订单信息统计，充值账户信息
"""

class orderinfoDAL:
    def getList(self,start=None,end=None,Game=None,Chan=""
                ,Account=None,RechargeAccount=None,Server=None
                ,page=1, limit=20, filed='id', sort='ascending'):
        param = locals()
        sql = """
            SELECT ID,`Game`,`Chan`,`Role_Name`,`Account`,`RechargeAccount`,`Residue`,
            `OtherSideID`,`OtherSideName`,`Money`,`Server`,
             DATE_FORMAT(Create_Time, '%Y-%m-%d %H:%i') AS Create_Time
            FROM `yjjl_order_info`     
               {0}
               {1}
              """
        sql_where = """ WHERE {0} """
        sql_3param = SQL_LIKE(locals_filed=param,only_contain_field= ['Game','Chan','Account','RechargeAccount'])
        sql_where = sql_where.format(sql_3param)
        if start is not None:
            sql_where+=f" and Create_Time>='{start}' and Create_Time<ADDDATE('{end}', 1) "
        if Server is not None:
            sql_where += f" and Server='{Server}'  "

        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `yjjl_order_info`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()

    def getlist4down(self,start=None,end=None,Game=None,Chan=""
                ,Account=None,RechargeAccount=None,Server=None):
        param = locals()
        sql = """
            SELECT ID,`Game`,`Chan`,`Role_Name`,`Account`,`RechargeAccount`,`Residue`,
            `OtherSideID`,`OtherSideName`,`Money`,`Server`,
             DATE_FORMAT(Create_Time, '%Y-%m-%d %H:%i') AS Create_Time
            FROM `yjjl_order_info`     
               {0}             
              """
        sql_where = """ WHERE {0} """
        sql_3param = SQL_LIKE(locals_filed=param,only_contain_field= ['Game','Chan','Account','RechargeAccount'])
        sql_where = sql_where.format(sql_3param)
        if start is not None:
            sql_where+=f" and Create_Time>='{start}' and Create_Time<ADDDATE('{end}', 1) "
        if Server is not None:
            sql_where += f" and Server='{Server}'  "
        sql = sql.format(sql_where)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()


class accountInfoDAL:
    def getList(self,start=None,end=None,Game=None,Chan=""
                ,Account=None,PayUse=None,Residue_s=None,Residue_e=None
                ,Phone=None,Area=None,Money_s=None,Money_e=None,source=None
                ,page=1, limit=20, filed='id', sort='ascending'):
        param = locals()
        sql = """
        SELECT ID,Game,Chan,Account,Discount,PayUse,Money
        ,Residue,source,Store,Phone,device
        ,Area,Imei,DATE_FORMAT(Rehcarge_Time, '%Y-%m-%d') AS Rehcarge_Time
        ,DATE_FORMAT(Create_Time, '%Y-%m-%d %H:%i:%s') AS Create_Time
        FROM recharge_account
               {0}
               {1}
              """
        sql_where = """ WHERE {0} """
        sql_3param = SQL_LIKE(locals_filed=param,
                              only_contain_field= ['Game','Chan','Account','PayUse','Phone','Area','source'])
        sql_where = sql_where.format(sql_3param)
        if start is not None:
            sql_where+=f" and Rehcarge_Time>='{start}' and Rehcarge_Time<ADDDATE('{end}', 1) "
        if Residue_s is not None:
            sql_where += f" and Residue>='{Residue_s}'  "
        if Residue_e is not None:
            sql_where += f" and Residue<='{Residue_e}'  "
        if Money_s is not None:
            sql_where += f" and Money>='{Money_s}'  "
        if Money_e is not None:
            sql_where += f" and Money<='{Money_e}'  "

        sql_order = SQL_ORDER(page, limit, filed, sort)
        sql = sql.format(sql_where, sql_order)
        # print(sql)
        sql_count = """
               SELECT count(id) as count
               FROM `recharge_account`         
               {0}            
              """
        sql_count = sql_count.format(sql_where)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                cursor.execute(sql_count)
                conn.commit()
                res1 = cursor.fetchone()
                return res, res1["count"]
        finally:
            conn.close()

    def getlist4down(self,start=None,end=None,Game=None,Chan=""
                ,Account=None,PayUse=None,Residue_s=None,Residue_e=None
                ,Phone=None,Area=None,Money_s=None,Money_e=None,source=None):
        param = locals()
        sql = """
        SELECT ID,Game,Chan,Account,Discount,PayUse,Money
        ,Residue,source,Store,Phone,device
        ,Area,Imei,DATE_FORMAT(Rehcarge_Time, '%Y-%m-%d') AS Rehcarge_Time
        ,DATE_FORMAT(Create_Time, '%Y-%m-%d %H:%i:%s') AS Create_Time
        FROM recharge_account   
               {0}             
              """
        sql_where = """ WHERE {0} """
        sql_3param = SQL_LIKE(locals_filed=param,
                              only_contain_field=['Game', 'Chan', 'Account', 'PayUse', 'Phone', 'Area', 'source'])
        sql_where = sql_where.format(sql_3param)
        if start is not None:
            sql_where += f" and Rehcarge_Time>='{start}' and Rehcarge_Time<ADDDATE('{end}', 1) "
        if Residue_s is not None:
            sql_where += f" and Residue>='{Residue_s}'  "
        if Residue_e is not None:
            sql_where += f" and Residue<='{Residue_e}'  "
        if Money_s is not None:
            sql_where += f" and Money>='{Money_s}'  "
        if Money_e is not None:
            sql_where += f" and Money<='{Money_e}'  "
        sql = sql.format(sql_where)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.fetchall()
                return res
        finally:
            conn.close()

    def deleteById(self, id=0):
        sql_1 = """
                DELETE 
                FROM  recharge_account
                WHERE ID='{0}'
           """
        sql_1 = sql_1.format(id)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

    def notExistAdd(self,Game=None,Chan=None, Account=None,Discount=None
                    ,PayUse=None,Money=None, Residue=None, source=None, Store=None,
               Phone=None,device=None,Area=None,Imei=None,Rehcarge_Time=None):
        param = locals()
        sql = """
               INSERT INTO  `recharge_account`({0},Create_Time)
               VALUES({1},now())
               """
        sql_field, sql_values = SQL_ADD_FIELD_VALUES(locals_filed=param)
        sql = sql.format(sql_field,sql_values)

        sql_1 = """
        SELECT count(id) AS count
        FROM `recharge_account`
        WHERE {0}      
        """
        sql_where = SQL_WHERE(locals_filed=param,only_contain_field=['Account'])
        sql_1 = sql_1.format(sql_where)
        # print(sql_1)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql_1)
                conn.commit()
                res_one = cursor.fetchone()
                # print(res_one)
                if res_one['count']>0:
                    res=0
                    return res
                else:
                    cursor.execute(sql)
                    conn.commit()
                    res = cursor.rowcount
                    return res
        finally:
            conn.close()


    def update(self, ID=None,Game=None,Chan=None, Account=None,Discount=None
                    ,PayUse=None,Money=None, Residue=None, source=None, Store=None,
               Phone=None,device=None,Area=None,Imei=None,Rehcarge_Time=None
               ):
        pram = locals()
        sql_set = SQL_UPDATE_SET(locals_filed=pram,exclusion_field=['ID'])
        sql = """
                 UPDATE 
                   `recharge_account`
                   SET  {1}
                   WHERE ID = '{0}'
                 """
        sql = sql.format(ID, sql_set)
        # print(sql)
        try:
            conn = conn_a()
            with conn.cursor() as cursor:
                cursor.execute(sql)
                conn.commit()
                res = cursor.rowcount
                return res
        finally:
            conn.close()

if __name__=="__main__":
    res =1;
    lDal = accountInfoDAL()
    res = lDal.getList()

    print(res)












